#!/bin/bash
# 针对DIM层建表开发操作、建模

HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/sqoop

${HIVE_HOME} -e "
-- 在读人数表 分区字段dt 布隆索引字段studying_student_count和studying_date
DROP TABLE itcast_dimen.class_studying_student_count_dimen;
CREATE TABLE IF NOT EXISTS itcast_dimen.class_studying_student_count_dimen (
    id                     INT,
    school_id              INT COMMENT '校区id',
    subject_id             INT COMMENT '学科id',
    class_id               INT COMMENT '班级id',
    studying_student_count INT COMMENT '在读班级人数',
    studying_date          STRING COMMENT '在读日期'
) COMMENT '在读班级的每天在读学员人数'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY','orc.bloom.filter.columns' = 'studying_student_count,studying_date');
-- 班级课程表 分区字段dt 布隆索引字段class_id和class_date
DROP TABLE itcast_dimen.course_table_upload_detail_dimen;
CREATE TABLE IF NOT EXISTS itcast_dimen.course_table_upload_detail_dimen (
    id                  INT COMMENT 'id',
    base_id             INT COMMENT '课程主表id',
    class_id            INT COMMENT '班级id',
    class_date          STRING COMMENT '上课日期',
    content             STRING COMMENT '课程内容',
    teacher_id          INT COMMENT '老师id',
    teacher_name        STRING COMMENT '老师名字',
    job_number          STRING COMMENT '工号',
    classroom_id        INT COMMENT '教室id',
    classroom_name      STRING COMMENT '教室名称',
    is_outline          INT COMMENT '是否大纲 0 否 1 是',
    class_mode          INT COMMENT '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',
    is_stage_exam       INT COMMENT '是否阶段考试（0：否 1：是）',
    is_pay              INT COMMENT '代课费（0：无 1：有）',
    tutor_teacher_id    INT COMMENT '晚自习辅导老师id',
    tutor_teacher_name  STRING COMMENT '辅导老师姓名',
    tutor_job_number    STRING COMMENT '晚自习辅导老师工号',
    is_subsidy          INT COMMENT '晚自习补贴（0：无 1：有）',
    answer_teacher_id   INT COMMENT '答疑老师id',
    answer_teacher_name STRING COMMENT '答疑老师姓名',
    answer_job_number   STRING COMMENT '答疑老师工号',
    remark              STRING COMMENT '备注',
    create_time         STRING COMMENT '创建时间'
) COMMENT '班级课表明细表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY','orc.bloom.filter.columns' = 'class_id,class_date');
-- 班级作息表 分区字段dt 布隆索引字段id和class_id
DROP TABLE itcast_dimen.class_time_dimen;
CREATE TABLE IF NOT EXISTS itcast_dimen.class_time_dimen (
    id                    INT,
    class_id              INT COMMENT '班级id',
    morning_template_id   INT COMMENT '上午出勤模板id',
    morning_begin_time    STRING COMMENT '上午开始时间',
    morning_end_time      STRING COMMENT '上午结束时间',
    afternoon_template_id INT COMMENT '下午出勤模板id',
    afternoon_begin_time  STRING COMMENT '下午开始时间',
    afternoon_end_time    STRING COMMENT '下午结束时间',
    evening_template_id   INT COMMENT '晚上出勤模板id',
    evening_begin_time    STRING COMMENT '晚上开始时间',
    evening_end_time      STRING COMMENT '晚上结束时间',
    use_begin_date        STRING COMMENT '使用开始日期',
    use_end_date          STRING COMMENT '使用结束日期',
    create_time           STRING COMMENT '创建时间',
    create_person         INT COMMENT '创建人',
    remark                STRING COMMENT '备注'
) COMMENT '班级作息时间表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY','orc.bloom.filter.columns' = 'id,class_id');
"

# 在读人数表导入
${SQOOP_HOME} import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT *, CURRENT_DATE() AS dt FROM class_studying_student_count where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table class_studying_student_count_dimen \
--split-by id \
-m 4

# 班级课程表导入
${SQOOP_HOME} import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT *, CURRENT_DATE() AS dt FROM course_table_upload_detail where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table course_table_upload_detail_dimen \
--split-by id \
-m 4

# 班级作息表导入
${SQOOP_HOME} import \
--connect jdbc:mysql://192.168.10.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT *, CURRENT_DATE() AS dt FROM tbh_class_time_table where $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table class_time_dimen \
--split-by id \
-m 4